﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Xml;

namespace CarFrameWork.Common
{
    class SqlWork//数据库操作类
    {
        //static string dbserver = "";
        /// <summary>
        /// 保存数据功能
        /// </summary>
        /// <param name="str"></param>
        public SqlWork(string str)
        {
            //string MyConn = "Data Source=DISCOVER;Initial Catalog=cheqiaoxuanjia;Trusted_Connection=SSPI";
            string MyConn = dbconn();
            //string MyConn = "Data Source=10.63.201.220;Initial Catalog=haamcw;Persist Security Info=True;User ID=haamcw;Password=proxy3048";
            SqlConnection MyConnection = new SqlConnection(MyConn);
            //str = "INSERT INTO 垂向力最大时 VALUES (" + m_pC.Text + "," + m_ph.Text + "," + m_pd.Text + ")";
            SqlCommand MyCommand = new SqlCommand(str, MyConnection);
            //SqlCommand MyCommand = new SqlCommand("INSERT INTO 垂向力最大时 VALUES (10,1222,222)", MyConnection);
            try
            {
                MyConnection.Open();
                MyCommand.ExecuteNonQuery();
                if (MyConnection.State == ConnectionState.Open)
                {
                    MessageBox.Show("保存成功");
                }
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误!");
            }
        }

        /// <summary>
        /// 读数据库配置文件
        /// </summary>
        /// <returns>返回连接字符串</returns>
        public static string dbconn()//数据库配置
        {
            ////XmlDocument xml1 = new XmlDocument();
            ////xml1.Load(@"dbconn.xml");
            ////XmlNode oNode = xml1.SelectSingleNode(@"//dbconfig");
            ////return oNode.Attributes["dbserver"].Value.ToString();
            //MessageBox.Show("数据库服务器名：" + dbserver, "测试");
            //return "Data Source=10.63.201.220;Initial Catalog=haamcw;Persist Security Info=True;User ID=haamcw;Password=proxy3048";
            //return "Data Source=NENGXIAO-ECCA1F;Initial Catalog=CarWorkDB;Persist Security Info=True;User ID=sa;Password=123456";
            return Global.DataBase_LinkString;
        }

        /// <summary>
        /// 从数据库中读取单个值
        /// </summary>
        /// <param name="command">查询字符串</param>
        /// <param name="Number">要查询的字段名称</param>
        /// <returns></returns>
        public static string rogatory(string command,string Number)
        {
            string returnStr = "";
            try
            {
                string MyConn = dbconn();
                SqlConnection MyConnection = new SqlConnection(MyConn);
                SqlDataReader datareader = null;
                MyConnection.Open();
                SqlCommand MyCommand = new SqlCommand(command, MyConnection);
                datareader = MyCommand.ExecuteReader();
                while (datareader.Read())
                {
                    returnStr = datareader[Number].ToString();
                }
                datareader.Close();
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误！");
            }
            return returnStr;
        }

        /// <summary>
        /// 验证功能
        /// </summary>
        /// <param name="command">查询字符串</param>
        /// <returns>布尔值确认是否成功</returns>
        public static bool validate(string command)
        {
            bool bRet = false;
            //string sql = "select count(*) from Users where UserAccount='" + txtLoginName.Text + "' and UserPwd='" + txtPwd.Text + "'";
            //MessageBox.Show(sql, "显示sql语句");
            try
            {
                string MyConn = /*"Data Source=" + */SqlWork.dbconn();//+ ";Initial Catalog=AxlesData;Trusted_Connection=SSPI";
                SqlConnection MyConnection = new SqlConnection(MyConn);
                MyConnection.Open();
                SqlCommand MyCommand = new SqlCommand(command, MyConnection);
                int state = Convert.ToInt32(MyCommand.ExecuteScalar());
                if (state == 0 || state > 1)
                {
                    //MessageBox.Show("用户不存在，请检测用户名和密码是否正确!");
                    //this.Close();
                    bRet = false;
                }
                else
                {
                    //MessageBox.Show("登入成功!");
                    bRet = true;
                }
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误！");
            }
            return bRet;
        }
        /// <summary>
        /// 批量读取数据
        /// </summary>
        /// <param name="command">查询语句</param>
        /// <returns>返回SqlDataReader数据留</returns>
        public static SqlDataReader TiredQuery(string command)
        {
            SqlDataReader datareader = null;
            try
            {
                string MyConn = dbconn();
                SqlConnection MyConnection = new SqlConnection(MyConn);
                MyConnection.Open();
                SqlCommand MyCommand = new SqlCommand(command, MyConnection);
                datareader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
                //MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误！");
            }
            return datareader;
        }

        /// <summary>
        /// 查询数据库中型号是否已经存在
        /// </summary>
        /// <param name="command">查询语句</param>
        /// <returns>返回是否存在</returns>
        public static bool ModelQuery(string command)
        {
            bool Judge=false;
            try
            {
                string MyConn = SqlWork.dbconn();
                SqlConnection MyConnection = new SqlConnection(MyConn);
                MyConnection.Open();
                SqlCommand MyCommand = new SqlCommand(command, MyConnection);
                int state = Convert.ToInt32(MyCommand.ExecuteScalar());
                if (state == 0 || state > 1)
                {
                    MessageBox.Show("此型号不存在！");
                    Judge = true;
                }
                else
                {
                    MessageBox.Show("此型号存在！");
                    Judge = false;
                }
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误！");
            }
            return Judge;
        }

       /// <summary>
       /// 添加记录
       /// </summary>
       /// <param name="one">SQL插入语句</param>
        /// <param name="two">SQL插入语句</param>
        /// <param name="three">SQL插入语句,可为空</param>
       /// <returns>返回是否插入成功</returns>
        public static bool AddData(string one,string two,string three)
        {
            bool Judge = false;
            string MyConn = dbconn();
            SqlConnection MyConnection = new SqlConnection(MyConn);
            MyConnection.Open();
            SqlCommand command = MyConnection.CreateCommand();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = MyConnection.BeginTransaction();

            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection = MyConnection;
            command.Transaction = transaction;

            try
            {
                command.CommandText =one;
                command.ExecuteNonQuery();

                command.CommandText =two;
                command.ExecuteNonQuery();

                if (three != "")
                {
                    command.CommandText = three;
                    command.ExecuteNonQuery();
                }
                // Attempt to commit the transaction.
                transaction.Commit();
                Judge = true;
            }
            catch 
            {
                transaction.Rollback();
                Judge = false;
            }
            return Judge;
        }
        /// <summary>
        /// 数据库单条语句执行方法
        /// </summary>
        /// <param name="command">语句字符串</param>
        /// <returns>是否执行成功</returns>
        public static bool AddSingle(string command)
        {
            bool Judge = false;
            string MyConn = dbconn();
            SqlConnection MyConnection = new SqlConnection(MyConn);
            SqlCommand MyCommand = new SqlCommand(command, MyConnection);
            try
            {
                MyConnection.Open();
                MyCommand.ExecuteNonQuery();
                if (MyConnection.State == ConnectionState.Open)
                {
                    Judge = true;
                }
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误!");
                Judge = false;
            }
            return Judge;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="strSql">查询数据库语句</param>
        /// <returns></returns>
        public static DataTable select(string strSql, string strFlag)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(dbconn());
            try
            {

                DataSet ds = new DataSet();
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSql, conn);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);

                sda.Fill(ds, strFlag);

                dt = ds.Tables[strFlag];
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误！");
                conn.Close();
            }
            return dt;
        }

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="strSql">sql文</param>
        /// <returns>是否执行成功</returns>
        public static bool ExecuteSql(string strSql)
        {
            SqlConnection conn = new SqlConnection(dbconn());
            SqlCommand cmd = new SqlCommand(strSql, conn);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误!");
                conn.Close();
                return false;
            }
        }        
    }

}
